from extensions import db
from utils.decorators import with_application
from log import logger


@with_application
def insert_users():
    
    db.session.execute("delete from silo.group_users")
    db.session.commit()
    # 根据关注人数排序查询本次需要推送的公众号
    sort_sql = """
    select count(*) as rank, wuu.wx_app_id, wa.`desc` from for_os.wechat_union_users wuu
join for_apis.wx_apps wa on wa.id = wuu.wx_app_id 
where wuu.subscribe = 1
and wuu.wx_app_id in  (82, 99, 62, 109, 122, 119, 128, 124, 114, 111, 110, 112, 125, 113,116, 121, 115, 129, 127, 123, 132, 117, 133, 131, 118, 134)
group by wuu.wx_app_id 
order by rank desc 
    """
    # 2月8日推送sql
    push_sql ="""
    insert INTO silo.group_users ( openid, user_id, app_id, wx_app_id ) SELECT DISTINCT
wuu.openid,
wuu.user_id,
w.app_id,
{}
	
FROM
	for_os.wechat_union_users wuu
-- 	JOIN for_apis_test.wx_apps AS w ON wx_app_id = w.id
	JOIN for_apis.wx_apps AS w ON wuu.wx_app_id = w.id
	JOIN for_os.user_apps ua0 ON ua0.user_id = wuu.user_id and ua0.app_type = 'trial' and ua0.app_id= 1  -- 仅有体验课资格的人群
	LEFT JOIN for_os.user_app_records uar ON uar.user_id = wuu.user_id and uar.app_type = 'formal'  -- 曾获得过正式课资格的人群
	LEFT JOIN for_os.users u on u.id = wuu.user_id
	LEFT JOIN ninth_studio.staff_users su on su.s_unionid = u.unionid
	left join silo.group_users gu on gu.user_id = wuu.user_id
WHERE
  wuu.subscribe = 1 
	and uar.user_id is null 
	and gu.user_id is null
    and su.id is null
	and ua0.created_at >= '2019-08-01' and ua0.created_at < '2020-02-01'
	and wuu.wx_app_id = {}
    """
    apps = db.session.execute(sort_sql)
    for app in apps:
        # app[0] 关注人数 app[1] wx_app_id 
        logger.info("从公众号【{} - {}】拷贝数据......".format(app[2], app[1]))
        sql = push_sql.format(app[1], app[1])
        db.session.execute(sql)
        db.session.commit()
    
if __name__ == '__main__':
    insert_users()